﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Linq.Expressions;
using System.Data.Common;
using System.Data.SqlClient;
using System.Collections.ObjectModel;
using System.Reflection;

namespace CH.Study.ConsoleApp.ExpressionTree
{
    public abstract class QueryExpressionTranslator : QueryExpressionVisitor
    {

        protected StringBuilder sb;
        protected Expression expression;
        protected Dictionary<string, string> tableAliasList;
        protected int index = 0;
        protected List<DbParameter> parameter { get; set; }
        public QueryExpressionTranslator()
        {

        }
        public abstract string Translate(Expression expression);
        public virtual DbParameter[] GetParameter()
        {
            return this.parameter.ToArray();
        }

        private TableExpression GetTableExpression(Type type)
        {

            var attributes = type.GetCustomAttributes(typeof(TableMappingAttribute), true);
            string tableName = string.Empty;
            string primaryKey = string.Empty;
            foreach (var attribute in attributes)
            {
                TableMappingAttribute a = attribute as TableMappingAttribute;
                if (a != null)
                {
                    tableName = a.Name;
                    primaryKey = a.PrimaryKey;
                }
            }
            return new TableExpression(Expression.Constant(type), tableName, tableAliasList[tableName], primaryKey);

        }
        private void AddTableAlias(TableExpression te)
        {
            if (tableAliasList == null)
            {
                tableAliasList = new Dictionary<string, string>();

            }
            if (!tableAliasList.ContainsKey(te.Name))
            {
                tableAliasList.Add(te.Name, te.Alias);
            }
        }
        #region VisitCustomExpression
        protected override Expression VisitSelectExpression(SelectExpression se)
        {

            AddTableAlias(se.FromExp.Table);
            if (se.InnerJoinExp.Expression != null) 
            {
                AddTableAlias(se.InnerJoinExp.InnerTable);
            }
            this.Visit(se.TakeExp);
            this.Visit(se.ColumnExp);
            this.Visit(se.FromExp);
            this.Visit(se.InnerJoinExp);
            this.Visit(se.WhereExp);
            this.Visit(se.SkipExp);
            this.Visit(se.GroupExp);
            this.Visit(se.OrderExp);
            return se;
        }
        protected override Expression VisitDeleteExpression(DeleteExpression deleteExpression)
        {

            AddTableAlias(deleteExpression.Table);
            TableExpression te = deleteExpression.Table;
            sb.Append("DELETE FROM ");
            sb.Append(te.Name);
            if (deleteExpression.Expression != null)
            {
                sb.Append(" WHERE ");
                this.Visit(deleteExpression.Expression);
            }

            return deleteExpression.Expression;
        }
        protected override Expression VisitInsertExpression(InsertExpression insertExpression)
        {

            if (insertExpression.Expression != null)
            {
                ConstantExpression ce = insertExpression.Expression as ConstantExpression;

                AddTableAlias(insertExpression.Table);
                TableExpression te = insertExpression.Table;
                sb.Append("INSERT INTO ");
                sb.Append(te.Name);
                sb.Append("(");
                List<ConstantExpression> list = new List<ConstantExpression>();
                var cp = ce.Type.GetProperty("ChangedProperties");
                Dictionary<string, object> cpv = (Dictionary<string, object>)cp.GetValue(ce.Value, null);
                int index = 0;
                foreach (var item in cpv)
                {
                    sb.Append("[" + item.Key + "]");
                    list.Add(Expression.Constant(item.Value));
                    if (index++ != cpv.Count - 1)
                    {
                        sb.Append(",");
                    }
                }
                sb.Append(") VALUES (");
                for (int i = 0; i < list.Count; i++)
                {
                    this.Visit(list[i]);
                    if (i != list.Count() - 1)
                    {
                        sb.Append(",");
                    }
                }


                sb.Append(")");



            }

            return insertExpression.Expression;
        }
        protected override Expression VisitUpdateExpression(UpdateExpression updateExpression)
        {
            AddTableAlias(updateExpression.Table);
            TableExpression te = updateExpression.Table;
            object obj = updateExpression.obj;


            sb.Append("UPDATE ");
            sb.Append(te.Name);
            sb.Append(" SET ");
            var cp = obj.GetType().GetProperty("ChangedProperties");
            Dictionary<string, object> cpv = (Dictionary<string, object>)cp.GetValue(obj, null);
            int index = 0;
            foreach (var item in cpv)
            {
                sb.Append("[" + item.Key + "] = ");
                this.Visit(Expression.Constant(item.Value));
                if (index++ != cpv.Count - 1)
                {
                    sb.Append(",");
                }
            }
            if (updateExpression.Expression != null)
            {
                sb.Append(" WHERE ");
                this.Visit(updateExpression.Expression);

            }



            return updateExpression.Expression;
        }
        protected override Expression VisitColumnExpression(ColumnExpression columnExpression)
        {
            ConstantExpression ce = columnExpression.Expression as ConstantExpression;
            if (ce != null)
            {
                //初始化的时候如果没有Select函数 那么默认为 * 
                //QueryExpressionAnalyse--->   se.ColumnExp = new ColumnExpression(Expression.Constant("*"));
                sb.Append(ce.Value);
            }
            else
            {

                if (columnExpression.Expression.NodeType == ExpressionType.Call)
                {

                    //处理聚合函数类型 例如
                    //ctx.Table<MyHome>().Where(p => p.ID > 100 && p.ID < 200).Count();
                    //ctx.Table<MyHome>().Where(p => p.ID > 100 && p.ID < 200).Sum(p => p.ID);
                    MethodCallExpression methodCallExpression = columnExpression.Expression as MethodCallExpression;
                    if (methodCallExpression.Method.Name == "Average")
                    { 
                        sb.Append("AVG");
                    }
                    else
                    {
                        sb.Append(methodCallExpression.Method.Name.ToUpper());
                    }
                   
                    sb.Append("(");
                    if (methodCallExpression.Method.Name == "Count")
                    {
                        sb.Append("*");
                    }
                    else
                    {
                        if (methodCallExpression.Arguments.Count > 1)
                        {
                            this.Visit(methodCallExpression.Arguments[1]);
                        }
                    }
                    sb.Append(") ");
                }
                else
                {
                    //处理多个列
                    //ctx.Table<MyHome>().Where(p => p.ID > 100 && p.ID < 200).Select(p => new MyHome()).ToList();
                    //ctx.Table<MyHome>().Where(p => p.ID > 100 && p.ID < 200).Select(p => new MyHome() { ID = p.ID, Name = p.Name }).ToList();
                    LambdaExpression lambda = (LambdaExpression)StripQuotes(columnExpression.Expression);
                    if (lambda.Body.NodeType == ExpressionType.MemberInit)
                    {
                        MemberInitExpression mie = lambda.Body as MemberInitExpression;
                        if (mie.Bindings.Count > 0)
                        {
                            //Select(p => new MyHome() { ID = p.ID, Name = p.Name })
                            this.Visit(lambda.Body);
                        }
                        else
                        {
                            //Select(p => new MyHome())
                            sb.Append(" * ");
                        }
                    }
                    else
                    {
                        //格式错误的时候 默认 *
                        sb.Append(" * ");
                    }

                }
            }
            return columnExpression;
        }
        protected override Expression VisitGroupExpression(GroupExpression groupExpression)
        {
            if (groupExpression.Expression != null)
            {
                LambdaExpression lambda = (LambdaExpression)StripQuotes(groupExpression.Expression);
                if (lambda.Body.NodeType == ExpressionType.MemberInit)
                {
                    MemberInitExpression mie = lambda.Body as MemberInitExpression;
                    if (mie.Bindings.Count > 0)
                    {
                        sb.Append(" GROUP BY ");
                        this.Visit(lambda.Body);
                    }
                }
            }

            return groupExpression;
        }
        protected override Expression VisitWhereExpression(WhereExpression whereExpression)
        {
            if (whereExpression.Expression != null)
            {
                sb.Append(" WHERE ");
                this.Visit(whereExpression.Expression);
            }
            return whereExpression;
        }
        protected override Expression VisitTakeExpression(TakeExpression takeExpression)
        {
            if (takeExpression.Expression != null)
            {

                ConstantExpression ce = takeExpression.Expression as ConstantExpression;
                if (ce != null)
                {
                    sb.Append("SELECT TOP ");
                    sb.Append(ce.Value);
                    sb.Append(" ");
                }
            }
            else
            {
                sb.Append("SELECT ");
            }
            return takeExpression;
        }
        protected override Expression VisitOrderExpression(OrderExpression orderExpression)
        {

            if (orderExpression.OrderCollection.Count > 0)
            {

                sb.Append(" ORDER BY ");
                for (int i = 0; i < orderExpression.OrderCollection.Count; i++)
                {
                    //方法 Asc(ID)
                    MethodCallExpression mce = orderExpression.OrderCollection[i] as MethodCallExpression;
                    //输出 ID
                    this.Visit(mce.Arguments[0]);
                    //输出 ASC
                    this.Visit(mce.Arguments[1]);
                    if (i != orderExpression.OrderCollection.Count - 1)
                    {
                        //最后一个元素 不加分隔符逗号
                        //Order By ID ASC,NAME DESC
                        sb.Append(",");
                    }
                }

            }
            return orderExpression;
        }
        protected override Expression VisitSkipExpression(SkipExpression skipExpression)
        {

            if (skipExpression.Expression != null)
            {
                ConstantExpression ce = skipExpression.Expression as ConstantExpression;
                SelectExpression se = this.expression as SelectExpression;
                if (ce != null)
                {
                    if (se.IsHaveWhere)
                    {
                        sb.Append(" AND ");
                    }
                    TableExpression te = se.FromExp.Table;
                    sb.Append("(");
                    sb.Append(te.Alias + "." + te.PrimaryKey);
                    sb.Append(" NOT IN(SELECT TOP ");
                    sb.Append(ce.Value);
                    sb.Append(" " + te.Alias + "." + te.PrimaryKey + " ");
                    this.Visit(se.FromExp);
                    this.Visit(se.InnerJoinExp);
                    this.Visit(se.WhereExp);
                    this.Visit(se.GroupExp);
                    this.Visit(se.OrderExp);
                    sb.Append(")) ");
                }
            }
            return skipExpression;
        }
        protected override Expression VisitFromExpression(FromExpression fromExpression)
        {
            sb.Append(" FROM ");
            sb.Append(fromExpression.Table.Name);
            sb.Append(" AS ");
            sb.Append(fromExpression.Table.Alias);


            return fromExpression;
        }
        protected override Expression VisitInnerJoinExpression(InnerJoinExpression innerJoinExpression)
        {
            if (innerJoinExpression.Expression != null)
            {
                sb.Append(" INNER JOIN ");
                sb.Append(innerJoinExpression.InnerTable.Name);
                sb.Append(" AS ");
                sb.Append(innerJoinExpression.InnerTable.Alias);
                sb.Append(" ON ");
                this.Visit(innerJoinExpression.OutKeyExp);
                sb.Append("=");
                this.Visit(innerJoinExpression.InnerKeyEx);
                sb.Append(" ");
            }


            return innerJoinExpression;
        }

        #endregion
        #region VisitExpression
        protected override Expression VisitMethodCall(MethodCallExpression m)
        {
            if (m.Method.Name == "Count" || m.Method.Name == "Max" || m.Method.Name == "Min" || m.Method.Name == "Sum")
            {
                //处理Select(p => new MyHome() { ID = p.Key.ID.Count(), Email = p.Key.Email.Max() })
                //输出 COUNT
                sb.Append(m.Method.Name.ToUpper());
                //输出(ID)
                sb.Append("(");
                if (m.Arguments.Count > 0)
                {
                    this.Visit(m.Arguments[0]);
                }
                else
                {
                    sb.Append("1");
                }
                sb.Append(")");
            }
            else if (m.Method.Name == "Like")
            {
                //(p => p.Email.Like("%t%")) Where 条件
                //输出 Email
                sb.Append("(");
                this.Visit(m.Arguments[0]);
                //输出 Like 
                sb.Append(" " + m.Method.Name + " ");
                this.Visit(m.Arguments[1]);
                sb.Append(")");

            }
            else if (m.Method.Name == "In" || m.Method.Name == "NotIn")
            {
                int[] array = null;
                string arrayStr = string.Empty;
                ConstantExpression ce = null;
                MemberExpression me = m.Arguments[1] as MemberExpression;
                if (me != null)
                {
                    //处理这种方式
                    //int[] array = new int []{ 1, 2, 4, 5, 6 };
                    //exp.And(p => p.ID.In(array)); 这种方式会生成一个匿名类来封装array 所以只能使用反射的方式获取值
                    ce = (ConstantExpression)me.Expression;
                    array = ce.Type.GetFields().First().GetValue(ce.Value) as int[];
                }
                else
                {
                    //处理这种方式 exp.And(p => p.ID.In(new int[] { 1, 2, 4, 5, 6 }));
                    NewArrayExpression nae = m.Arguments[1] as NewArrayExpression;
                    array = nae.Expressions.Select(p => (int)(((ConstantExpression)p).Value)).ToArray();
                }

                sb.Append("(");
                this.Visit(m.Arguments[0]);
                if (m.Method.Name == "In")
                {
                    sb.Append(" IN ");
                }
                else
                {
                    sb.Append(" NOT IN ");
                }
                sb.Append("(");

                for (int i = 0; i < array.Length; i++)
                {
                    arrayStr += array[i];
                    if (i < array.Length - 1)
                    {
                        arrayStr += ",";
                    }
                }

                sb.Append(arrayStr);
                sb.Append("))");

            }
            else if (m.Method.Name == "As")
            {
                //处理Select(p => new MyHome() { ID = p.Key.ID.Count().As("ID"), Email = p.Key.Email.Max().As("Email") })
                //输出 COUNT(ID)
                this.Visit(m.Arguments[0]);
                //输出 AS ID
                if (m.Arguments.Count > 1)
                {
                    ConstantExpression ce = m.Arguments[1] as ConstantExpression;
                    sb.Append(" AS " + ce.Value + " ");
                }

            }
            else if (m.Method.Name == "ToString")
            {
                //忽略ToString 方法
                this.Visit(m.Object);
            }

            return m;
        }
        protected override Expression VisitBinary(BinaryExpression b)
        {
            sb.Append("(");
            this.Visit(b.Left);
            switch (b.NodeType)
            {
                case ExpressionType.And:
                case ExpressionType.AndAlso:
                    sb.Append(" AND ");
                    break;
                case ExpressionType.Or:
                    sb.Append(" OR");
                    break;
                case ExpressionType.Equal:
                    sb.Append(" = ");
                    break;
                case ExpressionType.NotEqual:
                    sb.Append(" <> ");
                    break;
                case ExpressionType.LessThan:
                    sb.Append(" < ");
                    break;
                case ExpressionType.LessThanOrEqual:
                    sb.Append(" <= ");
                    break;
                case ExpressionType.GreaterThan:
                    sb.Append(" > ");
                    break;
                case ExpressionType.GreaterThanOrEqual:
                    sb.Append(" >= ");
                    break;
                default:
                    throw new NotSupportedException(string.Format("不支持操作符{0}", b.NodeType));
            }
            this.Visit(b.Right);
            sb.Append(")");
            return b;
        }
        protected override Expression VisitParameter(ParameterExpression p)
        {
            sb.Append(" " + p.Name + " ");
            return p;
        }
        protected override Expression VisitMemberAccess(MemberExpression m)
        {
            TableExpression te = GetTableExpression(m.Expression.Type);
            sb.Append(te.Alias + ".[" + m.Member.Name + "]");
            return m;
        }
        protected override IEnumerable<MemberBinding> VisitBindingList(ReadOnlyCollection<MemberBinding> original)
        {

            for (int i = 0; i < original.Count; i++)
            {
                MemberBinding b = this.VisitBinding(original[i]);
                if (i != original.Count - 1)
                {
                    sb.Append(",");
                }
            }
            return original;
        }
        #endregion

    }
}
